﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel;
/******************************************************************************************************************
 * 
 * 
 * 说　明： SQL脚本生成工具(版本：Version1.0.0)
 * 作　者：李朝强
 * 日　期：2017/03/04
 * 修　改：
 * 参　考：http://my.oschina.net/lichaoqiang/
 * 备　注：暂无...
 * 
 * 
 * ***************************************************************************************************************/
namespace DbExcelToSQL
{

    /// <summary>
    /// <![CDATA[主窗口]]>
    /// </summary>
    public partial class Form1 : Form
    {

        #region 成员
        /// <summary>
        /// 默认数据表
        /// </summary>
        private DataTable dtTable = new DataTable("table");

        /// <summary>
        /// 默认数据源
        /// </summary>
        private DataSet dsTables = null;

        /// <summary>
        /// 构造
        /// </summary>
        public Form1()
        {
            InitializeComponent();

            //初始化表结构
            dtTable.Columns.Add("Title", typeof(string));
            dtTable.Columns.Add("DataType", typeof(string));
            dtTable.Columns.Add("DataLength", typeof(string));
            dtTable.Columns.Add("AllowNull", typeof(string));
            dtTable.Columns.Add("IsPrimaryKey", typeof(string));
            dtTable.Columns.Add("Default", typeof(string));
            dtTable.Columns.Add("Identity", typeof(string));
            dtTable.Columns.Add("Description", typeof(string));
            dtTable.Columns.Add("Mark", typeof(string));

        }
        #endregion 成员

        #region 事件
        /// <summary>
        /// <![CDATA[窗体载入事件]]>
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                dgvData.AutoGenerateColumns = false;
                dgvData.DataSource = dtTable;
            }
            catch (Exception exception)
            {
                ShowMsg(exception.Message);
            }
        }

        /// <summary>
        /// <![CDATA[生成SQL]]>
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBuildSql_Click(object sender, EventArgs e)
        {
            try
            {
                BuidSql(); //生成SQL
                MessageBox.Show(@"生成成功！");
            }
            catch (Exception exception)
            {
                ShowMsg(exception.Message);
            }
        }


        /// <summary>
        /// <![CDATA[显示消息]]>
        /// </summary>
        /// <param name="format"></param>
        /// <param name="args"></param>
        private void ShowMsg(string format, params object[] args)
        {
            try
            {
                lblStatus.Text = string.Format(format, args);
            }
            catch (Exception exception)
            {
                ShowMsg(exception.Message);
            }
        }


        /// <summary>
        /// <![CDATA[拖动事件]]>
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_DragEnter(object sender, DragEventArgs e)
        {
            try
            {
                ShowMsg(@"正在导入...");
                if (e.Data.GetDataPresent(DataFormats.FileDrop))
                {
                    string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
                    string filename = files[0];
                    string strExtensionName = System.IO.Path.GetExtension(filename).ToLower().Trim('.');
                    FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);

                    IExcelDataReader excelReader = null;
                    if (strExtensionName == "xls")
                    {
                        //Choose one of either 1 or 2
                        //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                        excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                    }
                    else if (strExtensionName == "xlsx")
                    {
                        //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                        excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

                    }
                    excelReader.IsFirstRowAsColumnNames = false;
                    //Choose one of either 3, 4, or 5
                    //3. DataSet - The result of each spreadsheet will be created in the result.Tables
                    DataSet result = excelReader.AsDataSet();
                    dsTables = result;

                    //绑定列表
                    foreach (DataTable item in result.Tables) { cbxTables.Items.Add(item.TableName); }
                    cbxTables.SelectedIndex = 0;

                    Resolve(dsTables.Tables[0]);//默认显示第一个
                    excelReader.Dispose();

                }
                else
                {
                    ShowMsg(@"操作无效！");
                }
            }
            catch (Exception exception)
            {
                ShowMsg("拖动事件{0}", exception.Message);
            }
            finally
            {
                ShowMsg("数据加载完毕！");
            }
        }

        /// <summary>
        /// <![CDATA[选择项改变]]>
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cbxTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                dtTable.Clear();
                int i = 0;
                gpxContainer.Text = cbxTables.SelectedItem.ToString();
                DataTable dataTable = dsTables.Tables[cbxTables.SelectedItem.ToString()];
                Resolve(dataTable);

            }
            catch (Exception exception)
            {
                ShowMsg("选择表", exception.Message);
            }
        }

        /// <summary>
        /// <![CDATA[跳转链接]]>
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            try
            {
                System.Diagnostics.Process.Start("http://www.lichaoqiang.com/");
            }
            catch (Exception exception)
            {
                ShowMsg(exception.Message);
            }
        }
        #endregion 事件


        #region 方法
        /// <summary>
        /// <![CDATA[生成ＳＱＬ]]>
        /// </summary>
        private void BuidSql()
        {
            StringBuilder stbSqlText = new StringBuilder();
            //1>声明部分
            stbSqlText.AppendLine("--===============================================================================================");
            stbSqlText.AppendLine();
            stbSqlText.AppendLine("--创建日期：" + DateTime.Today);
            stbSqlText.AppendLine("--作    者：李朝强");
            stbSqlText.AppendLine("--参    考：http://www.lichaoqiang.com/");
            stbSqlText.AppendLine("--说    明：本代码有DbExcelToSQL工具生成");
            stbSqlText.AppendLine("--版    本：V1.1.1");
            stbSqlText.AppendLine();
            stbSqlText.AppendLine("--===============================================================================================");

            //2>循环表
            foreach (DataTable dataTable in dsTables.Tables)
            {
                string strTableName = string.Empty;//表英文名
                Match match = Regex.Match(dataTable.TableName, @"(\((?<TableName>\w+)\))");
                if (match.Success)
                {
                    //2.1>表头声明部分
                    strTableName = match.Groups["TableName"].Value;
                    stbSqlText.AppendLine("--===============================================================================================");
                    stbSqlText.AppendLine();
                    stbSqlText.AppendFormat("--说   明：{0}", dataTable.TableName);

                    stbSqlText.AppendLine();
                    stbSqlText.AppendFormat("--表   名：{0}", strTableName);

                    stbSqlText.AppendLine();
                    stbSqlText.AppendLine();
                    stbSqlText.AppendLine("--===============================================================================================");

                    stbSqlText.AppendFormat("CREATE TABLE [{0}]", strTableName);
                    stbSqlText.AppendLine("(");

                    //2.2>循环字段
                    StringBuilder stbPrimaryKey = new StringBuilder();//主键
                    stbPrimaryKey.AppendFormat("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ", strTableName);
                    stbPrimaryKey.AppendLine("(");

                    StringBuilder stbDescrption = new StringBuilder();//字段描述

                    #region 循环字段
                    //2.3>循环提取字段信息
                    int i = 0;
                    i = LocationStart(dataTable).Y + 1;
                    bool isHasPrimaryKey = false;//是否包含主键
                    for (; i < dataTable.Rows.Count; i++)
                    {
                        StringBuilder stbField = new StringBuilder();//字段信息
                        //2.3.1>局部变量
                        string strDataType = dataTable.Rows[i][3].ToString().ToLower();//数据类型
                        string strFieldName = dataTable.Rows[i][2].ToString();//字段
                        string strPrimaryKey = dataTable.Rows[i][6].ToString();//主键
                        string strDefault = dataTable.Rows[i][7].ToString();//默认值
                        string strIdentity = dataTable.Rows[i][8].ToString();//标示
                        string strAllowNull = dataTable.Rows[i][5].ToString().ToUpper();//是否允许为空
                        string strDescription = dataTable.Rows[i][9].ToString();//字段描述

                        if (string.IsNullOrEmpty(strDataType) || string.IsNullOrEmpty(strFieldName)) { continue; }//处理意外数据

                        stbField.AppendFormat("[{0}] ", strFieldName);//字段
                        stbField.AppendFormat("{0}", strDataType);//数据类型

                        //2.3.2>字段类型
                        if (strDataType.IndexOf("nvarchar", StringComparison.CurrentCulture) >= 0 ||
                            strDataType.IndexOf("varchar", StringComparison.CurrentCulture) >= 0)
                        {
                            stbField.AppendFormat("({0})", dataTable.Rows[i][4]);
                        }

                        //2.3.3>处理自增
                        if (Is(strIdentity))
                        {
                            stbField.AppendFormat("  IDENTITY(1,1) ");
                        }

                        //2.3.4>处理默认
                        if (!string.IsNullOrEmpty(strDefault)) stbField.AppendFormat(" DEFAULT {0} ", strDefault);

                        //2.3.5>是否允许为空
                        if (strAllowNull == "×" || strAllowNull == "N")
                        {
                            stbField.AppendFormat(" NOT NULL ");
                        }
                        else
                        {
                            stbField.AppendFormat(" NULL ");
                        }
                        //2.3.6>处理主键
                        if (Is(strPrimaryKey))
                        {
                            stbPrimaryKey.AppendFormat("{0} ASC", strFieldName);
                            stbPrimaryKey.Append(",");
                            isHasPrimaryKey = true;
                        }
                        stbField.Append(",");

                        //处理描述
                        string strFieldSql = stbField.ToString();
                        strFieldSql = PadRight(strFieldSql, 80);
                        strFieldSql = strFieldSql + " --" + strDescription;

                        stbField.AppendLine();

                        //2.3.7>扩展描述
                        stbDescrption.AppendFormat("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE'", strDescription);
                        stbDescrption.AppendFormat(",@level1name=N'{0}', @level2type=N'COLUMN',@level2name=N'{1}'", strTableName, strFieldName);
                        stbDescrption.AppendLine();
                        stbDescrption.AppendLine("GO");

                        stbSqlText.Append(strFieldSql);//追加文本
                        stbSqlText.AppendLine();

                    }
                    #endregion 循环字段

                    stbPrimaryKey.Remove(stbPrimaryKey.ToString().LastIndexOf(','), 1);
                    stbPrimaryKey.AppendLine(")");

                    //2.3>主键-是
                    if (isHasPrimaryKey)
                    {
                        stbSqlText.AppendLine(stbPrimaryKey.ToString());
                        stbSqlText.AppendLine(" WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]");
                    }
                    else
                    {
                        stbSqlText.Remove(stbSqlText.ToString().LastIndexOf(','), 1);
                        stbSqlText.AppendLine(")");
                    }

                    stbSqlText.AppendLine("GO");
                    stbSqlText.AppendLine();
                    stbSqlText.AppendLine(stbDescrption.ToString());
                    stbSqlText.AppendLine();
                }
                stbSqlText.AppendLine("GO");
            }
            //3>保存脚本
            string strSaveToPath = AppDomain.CurrentDomain.BaseDirectory;
            strSaveToPath = System.IO.Path.Combine(strSaveToPath, "生成SQL-" + DateTime.Now.ToString("yyy-MM-dd") + ".sql");
            File.WriteAllText(strSaveToPath, stbSqlText.ToString(), Encoding.Unicode);//写入文本文件
            System.Diagnostics.Process.Start(AppDomain.CurrentDomain.BaseDirectory);//打开默认存储目录
        }

        /// <summary>
        /// <![CDATA[标示]]>
        /// </summary>
        /// <param name="flag"></param>
        /// <returns></returns>
        private bool Is(string flag)
        {
            if (string.IsNullOrEmpty(flag)) return false;
            flag = flag.ToUpper();
            if (flag == "√" || flag == "Y")
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// <![CDATA[解析数据源]]>
        /// </summary>
        /// <param name="dtSource"></param>
        private void Resolve(DataTable dtSource)
        {
            dtTable.Clear();
            int i = 0;
            int s = LocationStart(dtSource).Y + 1;
            foreach (DataRow item in dtSource.Rows)
            {
                i++;
                if (i <= s) continue;
                if (string.IsNullOrEmpty(item[2].ToString())) continue;
                DataRow row = dtTable.NewRow();
                row["Title"] = item[2];
                row["DataType"] = item[3];
                row["DataLength"] = item[4];
                row["AllowNull"] = item[5];
                row["IsPrimaryKey"] = item[6];
                row["Default"] = item[7].ToString();
                row["Identity"] = item[8];
                row["Description"] = item[9];
                row["Mark"] = item[10];

                dtTable.Rows.Add(row);
            }
        }

        /// <summary>
        ///<![CDATA[定位开始]]>
        /// </summary>
        /// <returns></returns>
        private Point LocationStart(DataTable dataTable)
        {
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    string strCellText = dataTable.Rows[i][j].ToString();//字段名
                    if (strCellText == "字段名")
                    {
                        return new Point(j, i);
                    }
                }
            }
            return new Point(2, 10);
        }

        /// <summary>
        /// <![CDATA[填充空字符]]>
        /// </summary>
        /// <param name="input"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        private string PadRight(string input, int total)
        {
            while (input.Length < total)
            {
                input = input + " ";
            }
            return input;
        }
        #endregion 方法

    }
}
